package kr.hyunist.memoappproject.DAO;

import java.util.*;

import kr.hyunist.memoappproject.DTO.*;
import kr.hyunist.memoappproject.SQLiteHelper.*;
import android.content.*;
import android.database.*;
import android.database.sqlite.*;

public class MyMemoAppDAO {
	Cursor cursor;
	MyMemoAppSqlHelper mHelper;
	SQLiteDatabase db;
	MyMemo mMemo;
	
	public MyMemoAppDAO(Context context) {
		mHelper = new MyMemoAppSqlHelper(context);
		db = mHelper.getWritableDatabase();
	}
	
	public static MyMemoAppDAO open(Context context){
		return new MyMemoAppDAO(context);
	}
	
	public void close(){
		mHelper.close();
	}
	
	public ArrayList<MyMemo> selectMemoList(){
		ArrayList<MyMemo> arList = new ArrayList<MyMemo>();
		String sql = "select _id, title, content, date from memoTbl";
		cursor = db.rawQuery(sql, null);
		int id = 0; String title = null; String content = null; String date = null;
		
		while(cursor.moveToNext()){
			id = cursor.getInt(0);
			title = cursor.getString(1);
			content = cursor.getString(2);
			date = cursor.getString(3);
			mMemo = new MyMemo(id, title, content, date);
			arList.add(mMemo);
		}

		return arList;	
	}
	
	public boolean selectMemo(int id){
		String sql = "select _id, title, content, date from memoTbl where _id = '" + id + "';";
		
		try {
			db.rawQuery(sql, null);
		} catch(SQLException e) {
			return false;
		}
		
		return true;
	}
		
	public boolean insertMemo(String title, String content, String date){
		String sql = "insert into memoTbl values (null, '" + title + "', '" + content + "', '" + date + "');";

		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		
		return true;
	}
	
	public boolean updateMemo(int id, String title, String content, String date){
		String sql = "update memoTbl set title = '" + title + "', content = '" + content + "', date = '" + date + "' where _id=" + id + ";";
		
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		
		return true;
	}
	
	public boolean deleteMemo(int id){
		String sql = "delete from memoTbl where _id = '" + id + "';";
		
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		
		return true;
	}
		
	public ArrayList<MyMemo> searchSelected(String mTitle){
		ArrayList<MyMemo> arList = new ArrayList<MyMemo>();
		String sql = "select _id, title, content, date from memoTbl where title like'%" + mTitle + "%';";
		
		cursor = db.rawQuery(sql, null);
		int id = 0; String title = null; String content = null; String date = null;
		
		while(cursor.moveToNext()){
			id = cursor.getInt(0);
			title = cursor.getString(1);
			content = cursor.getString(2);
			date = cursor.getString(3);
			mMemo = new MyMemo(id, title, content, date);
			arList.add(mMemo);
		}
		
		return arList;
	}

	public ArrayList<MyMemo> arrayTitleSelected(){
		ArrayList<MyMemo> arList = new ArrayList<MyMemo>();
		String sql = "select _id, title, content, date from memoTbl order by title asc";
		
		cursor = db.rawQuery(sql, null);
		int id = 0; String title = null; String content = null; String date = null;
		
		while(cursor.moveToNext()){
			id = cursor.getInt(0);
			title = cursor.getString(1);
			content = cursor.getString(2);
			date = cursor.getString(3);
			mMemo = new MyMemo(id, title, content, date);
			arList.add(mMemo);
		}
		
		return arList;
	}
	
	
	public ArrayList<MyMemo> arrayContentSelected(){
		ArrayList<MyMemo> arList = new ArrayList<MyMemo>();
		String sql = "select _id, title, content, date from memoTbl order by content asc";
		
		cursor = db.rawQuery(sql, null);
		int id = 0; String title = null; String content = null; String date = null;
		
		while(cursor.moveToNext()){
			id = cursor.getInt(0);
			title = cursor.getString(1);
			content = cursor.getString(2);
			date = cursor.getString(3);
			mMemo = new MyMemo(id, title, content, date);
			arList.add(mMemo);
		}
		
		return arList;
	}
	
	public ArrayList<MyMemo> arrayDateSelected(){
		ArrayList<MyMemo> arList = new ArrayList<MyMemo>();
		String sql = "select _id, title, content, date from memoTbl order by date asc";
		
		cursor = db.rawQuery(sql, null);
		int id = 0; String title = null; String content = null; String date = null;
		
		while(cursor.moveToNext()){
			id = cursor.getInt(0);
			title = cursor.getString(1);
			content = cursor.getString(2);
			date = cursor.getString(3);
			mMemo = new MyMemo(id, title, content, date);
			arList.add(mMemo);
		}
		
		return arList;
	}
}
